﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace LogisticsCrawler.Core
{
    /// <summary>
    /// 分页器
    /// </summary>
    public sealed class SqlPager
    {
        /// <summary>
        /// 调用分页器获取数据
        /// </summary>
        /// <param name="conn">连接串</param>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名集合</param>
        /// <param name="orderCol">排序列名</param>
        /// <param name="pageSize">页尺寸</param>
        /// <param name="pageIdx">当前页</param>
        /// <param name="orderType">升降序，true-0为升序，false-非0为降序</param>
        /// <param name="condition">条件</param>
        /// <param name="primaryKey">主键</param>
        /// <returns>DataTable</returns>
        public static DataTable GetSqlDataByPager(string connstr,
            string tableName,string colName,string orderCol,
            int pageSize,int pageIdx,bool orderType,string condition,string primaryKey)
        {
            SqlConnection conn = new SqlConnection();

            try
            {
                // 连接项
                conn.ConnectionString = connstr;
                conn.Open();
                // 命令项
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "pagination";
                cmd.CommandType = CommandType.StoredProcedure;
                // 参数项
                SqlParameter pTableName = new SqlParameter("@tblName", tableName);
                SqlParameter pColName = new SqlParameter("@strGetFields", colName);
                SqlParameter pOrderCol = new SqlParameter("@fldName", orderCol);
                SqlParameter pPageSize = new SqlParameter("@PageSize", pageSize);
                SqlParameter pPageIdx = new SqlParameter("@PageIndex", pageIdx);
                SqlParameter pOrderType = new SqlParameter("@OrderType", orderType?0:1);
                SqlParameter pCondition = new SqlParameter("@strWhere", condition);
                SqlParameter pPrimaryKey = new SqlParameter("@PrimaryKey", primaryKey);
                cmd.Parameters.Add(pTableName);
                cmd.Parameters.Add(pColName);
                cmd.Parameters.Add(pOrderCol);
                cmd.Parameters.Add(pPageSize);
                cmd.Parameters.Add(pPageIdx);
                cmd.Parameters.Add(pOrderType);
                cmd.Parameters.Add(pCondition);
                cmd.Parameters.Add(pPrimaryKey);
                // 分割列
                string[] cols = SplitsColumnNames(colName,','); 
                // 数据表
                DataTable dt = new DataTable(tableName);
                foreach (string col in cols)
                    dt.Columns.Add(col);

                // 取数据
                SqlDataReader sr = cmd.ExecuteReader();
                while (sr.Read())
                {
                    // 创建行
                    DataRow row = dt.NewRow();
                    for (int i = 0; i < cols.Length; i++)
                        row[cols[i]] = sr.GetValue(i);
                    // 插入行
                    dt.Rows.Add(row);
                }
                // 返回结果集
                return dt;
            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
        }

        /// <summary>
        /// 将字符里的列表分解出来
        /// </summary>
        /// <returns></returns>
        internal static string[] SplitsColumnNames(string columns,char separator)
        {
            return columns.Split(new char[] {separator });
        }
    }    
}
